﻿
CREATE VIEW [dbo].[vw_PerformanceSample_Hourly]
AS

SELECT DATEADD(MINUTE,-1*DATEPART(MINUTE,MaxDateTimeUTC),MaxDateTimeUTC) As DateTimeUTC, *
FROM
	(
	SELECT 
		DATEPART(YEAR,DateTimeUTC) AS DateTimeUTCYear, 
		DATEPART(MONTH,DateTimeUTC) AS DateTimeUTCMonth,
		DATEPART(DAY,DateTimeUTC) AS DateTimeUTCDay,
		DATEPART(HOUR,DateTimeUTC) AS DateTimeUTCHour,
		DATEPART(MINUTE,DateTimeUTC) AS DateTimeUTCMinute,
		MAX(DateTimeUTC) As MaxDateTimeUTC,
		A.RoleRowId,
		A.InstanceNumber,
		A.PerformanceCounterRowId,
		AVG(A.SampleValue) AS AverageValue,
		MIN(A.SampleValue) AS MinValue,
		MAX(A.SampleValue) AS MaxValue,
		STDEV(A.SampleValue) As StdDev
	FROM [dbo].[PerformanceSample] A
	GROUP BY A.RoleRowId, A.InstanceNumber, A.PerformanceCounterRowId, DATEPART(YEAR,DateTimeUTC), DATEPART(MONTH,DateTimeUTC), DATEPART(DAY,DateTimeUTC), DATEPART(HOUR,DateTimeUTC), DATEPART(MINUTE,DateTimeUTC)
	) AS A